import pymysql
from datetime import datetime
import feedparser
import requests

rss_feed = {"netease": "http://news.163.com/special/00011K6L/rss_newsattitude.xml",
            "songshuhui": "http://songshuhui.net/feed",
            "ifeng": "http://news.ifeng.com/rss/index.xml"
            }


def insert(items):
    database = pymysql.connect(host='localhost', user='root', passwd='hzsszh', db='tododatabase', charset='utf8', port=3306)
    cur = database.cursor()
    t = items
    if t is None:
        t = (1, "Sina news", 'No', int(str(datetime.now().timestamp())[0:10]))
    else:
        pass
    print("PreData: ", t)
    try:
        # 注意：数据格式一定要符合。
        sql = "Insert into todolist (user_id, title, status, create_time) \
        values({0}, '{1}', '{2}', {3})".format(t[0], t[1], t[2], t[3])
        cur.execute(sql)
        database.commit()
        print("Insert Done.")
    except:
        database.rollback()
        print("Insert Failed.")
    cur.close()
    database.close()


def delete():
    database = pymysql.connect('localhost', 'hzsai', 'hzsszh', 'tododatabase', charset='utf8')
    cur = database.cursor()

    sql = "delete from todolist where ???? "
    print(sql)
    status = cur.execute(sql)
    if status == 1:
        print("Delete Done.")
    else:
        print("Delete Failed.")
        database.rollback()
    database.commit()
    cur.close()
    database.close()


def updata():
    database = pymysql.connect('localhost', 'hzsai', 'hzsszh', 'tododatabase', charset='utf8')
    cur = database.cursor()

    sql = "update todolist set status={0}".format("yes")
    status = cur.execute(sql)

    if status == 1:
        print("Update Done.")
    else:
        print("Update Failed.")
        database.rollback()
    database.commit()
    cur.close()
    database.close()


def query():
    database = pymysql.connect('localhost', 'hzsai', 'hzsszh', 'tododatabase', charset='utf8')

    cur = database.cursor()

    sql = "select id, user_id, title, create_time from todolist where id <= 2"

    status = cur.execute(sql)
    if status == 1:
        print("Query successfully.")
        data = cur.fetchone()
        print("id = ", data[0], "user_id = ", data[1], "title = ", data[2], "create_time = ", datetime.fromtimestamp(data[3]))
    else:
        print("Query Failed.")
        database.rollback()
    database.commit()
    cur.close()
    database.close()


if __name__ == '__main__':

    try:
        cnt = 0
        for key, value in rss_feed.items():
            cnt += 1
            feed = None
            try:
                feed = feedparser.parse(str(value))
            except:
                print("feedparse error.")
            # print(feed)
            if feed is not None:
                conts = feed['entries']
                for content in conts:
                    print("content")
                    print("content.title", content.title)
                    print("content.published", content.published)
                    ori_time = content.published
                    print(ori_time)
                    time = datetime(int(ori_time[0:4]), int(ori_time[5:7]), int(ori_time[8:10]), int(ori_time[11:13]),
                            int(ori_time[14:16]), int(ori_time[17:19]))
                    doe = "yes" if cnt % 2 else "no"
                    t = (cnt, content.title, doe, int(time.timestamp()))
                    print("Content : ", t)
                    insert(t)
            else:
                print("Error: ", key, value)
    except:
        print("Data transfer Error.")